<?php

class CompanyVisitors extends \Phalcon\Mvc\Model
{

    /**
     *
     * @var integer
     */
    public $id;

    /**
     *
     * @var string
     */
    public $ip;

    /**
     *
     * @var string
     */
    public $date;

    /**
     *
     * @var integer
     */
    public $counter;

    /**
     *
     * @var integer
     */
    public $company_id;

    /**
     *
     * @var integer
     */
    public $session_id;

    /**
     *
     * @var string
     */
    public $location;

    /**
     * Independent Column Mapping.
     */
    public function columnMap()
    {
        return array(
            'id' => 'id', 
            'ip' => 'ip', 
            'date' => 'date', 
            'counter' => 'counter', 
            'company_id' => 'company_id', 
            'session_id' => 'session_id', 
            'location' => 'location'
        );
    }

    public function beforeValidationOnCreate()
    {
        $this->date = CURR_DATE;
    }

    public function incCounter($user_id,$company_id){
        $cv = new CompanyVisitors();
        $location = array(
                "CITY"=>CITY,
                "LONG"=>LONG,
                "LAT"=>LAT
            );
        $data = array(
                'ip'=>IP_ADDRESS,
                'company_id'=>$company_id,
                'session_id'=>$user_id,
                'location'=> json_encode($location)
            );
        return @$cv->save($data);

    }
    public function countVisitors($company_id)
    {
        $phql ="SELECT COUNT(cv.id) as total_visitors 
                FROM CompanyVisitors cv
                WHERE cv.company_id = ?0";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$company_id));
        return $data[0]->total_visitors;      
    }

    public function listVisitors()
    {
        $phql ="SELECT ci.id as company_id,ci.name,COUNT(cv.id) as total_visitors 
                FROM ClientInfo ci
                LEFT JOIN CompanyVisitors cv ON cv.company_id = ci.id
                GROUP BY ci.id
                ORDER BY company_id ASC";
        $data = $this->modelsManager->executeQuery($phql);
        return $data;   
    }
    public function listVisitorsById($company_id)
    {
        $x = CompanyVisitors::find(array(
                "conditions"=>"company_id = ?0",
                "bind"=>array(0=>$company_id)
            ));
        return sizeof($x);
    }
    public function listVisitorsQuarterly($company_id){
        $phql = "SELECT count(cv.id) as total_visitors,YEAR(date) as yr,QUARTER(date) as q
                FROM ClientInfo ci
                LEFT JOIN CompanyVisitors cv on cv.company_id = ci.id
                WHERE ci.id = ?0
                GROUP BY ci.id,yr,q
                ";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$company_id));

        return $data;
    }
}
